package net.ojbk.dao;

import java.sql.ResultSet;
import java.sql.Statement;

import net.ojbk.database.DatabaseConnection;
import net.ojbk.po.UserData;

/**
 * User Table
 * 
 * 增加 一条记录
 * 删除 一条记录
 * 查询 一条记录
 * 修改 一条记录
 * 
 * @author twobox
 *
 */
public class UserDataDao {
	static final String tableName = "userData";
	DatabaseConnection dbconn; // 数据库连接控制对象
	Statement stmt; // sql语句 操作对象
	
	public UserDataDao() {
		dbconn = new DatabaseConnection();
		stmt = dbconn.getStatement();
	}
	public void finalize() {
		dbconn.close();
	}
	
	// userData(id, email, tel, idcard, grade, college, clas, registrationTime, lastIP)
	
	public boolean addOneRecord(UserData ud) {
		boolean flag = false;
		try {
			// INSERT INTO userData(id, email, tel, idcard, grade, college, clas, registrationTime, lastIP) VALUE('10001', '200562421@qq.com', '15170571912', '362226199711200017', '2016', '软件学院', '软件2016-2', '2018.3.29', '192.168.1.1');
			String sql = "INSERT INTO "
					+ tableName
					+ "(id, email, tel, idcard, grade, college, clas, registrationTime, lastIP) VALUE('"
					+ ud.getId()
					+ "', '"
					+ ud.getEmail()
					+ "', '"
					+ ud.getTel()
					+ "', '"
					+ ud.getIdcard()
					+ "', '"
					+ ud.getGrade()
					+ "', '"
					+ ud.getCollege()
					+ "', '"
					+ ud.getClas()
					+ "', '"
					+ ud.getRegistrationTime()
					+ "', '"
					+ ud.getLastIP()
					+ "');";
			stmt.executeUpdate(sql);
			flag = true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean deleteOneRecord(UserData ud) {
		boolean flag = false;
		try {
			// DELETE FROM userData WHERE id='10001';
			String sql = "DELETE FROM "
					+ tableName
					+ " WHERE id='"
					+ ud.getId()
					+ "';";
			stmt.executeUpdate(sql);
			flag = true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean updateOneRecord(UserData ud) {
		boolean flag = false;
		try {
			// UPDATE userData SET id='10001', email='200562421@qq.com', tel='12345678901', idcard='362226199711200017', grade='2016', college='软件学院', clas='软件2016-2', registrationTime='2018.3.29', lastIP='192.168.1.1' WHERE id='10001';
			String sql = "UPDATE "
					+ tableName
					+ " SET id='"
					+ ud.getId()
					+ "', email='"
					+ ud.getEmail()
					+ "', tel='"
					+ ud.getTel()
					+ "', idcard='"
					+ ud.getIdcard()
					+ "', grade='"
					+ ud.getGrade()
					+ "', college='"
					+ ud.getCollege()
					+ "', clas='"
					+ ud.getClas()
					+ "', registrationTime='"
					+ ud.getRegistrationTime()
					+ "', lastIP='"
					+ ud.getLastIP()
					+ "' WHERE id='"
					+ ud.getId()
					+ "';";
			stmt.executeUpdate(sql);
			return true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean queryOneRecord(UserData ud) {
		
		boolean flag = false;
		ResultSet re;
		try {
			// SELECT * FROM userData WHERE id='10001';
			//select * from userData where id='76670' or email='1531764720@qq.com'
			String sql="select * from userData where id='"
					+ud.getId()
					+ "' or email='"
					+ud.getEmail()
					+ "' or tel='"
					+ ud.getTel()
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				ud.setId(re.getString("id"));
				ud.setEmail(re.getString("email"));
				ud.setTel(re.getString("tel"));
				ud.setIdcard(re.getString("idcard"));
				ud.setGrade(re.getString("grade"));
				ud.setCollege(re.getString("college"));
				ud.setClas(re.getString("clas"));
				ud.setRegistrationTime(re.getString("registrationTime"));
				ud.setLastIP(re.getString("lastIP"));
				flag = true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public int getCount() {
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ ";";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
	public UserData[] queryAllRecod(){
		int count = this.getCount();
		UserData[] uds=new UserData[count+5]; // 防止
		ResultSet re;
		UserData ud;
		try {
			// SELECT * FROM userData
			String sql="SELECT * FROM "
					+ tableName
					+ ";";
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				ud = new UserData();
				ud.setId(re.getString("id"));
				ud.setEmail(re.getString("email"));
				ud.setTel(re.getString("tel"));
				ud.setIdcard(re.getString("idcard"));
				ud.setGrade(re.getString("grade"));
				ud.setCollege(re.getString("college"));
				ud.setClas(re.getString("clas"));
				ud.setRegistrationTime(re.getString("registrationTime"));
				ud.setLastIP(re.getString("lastIP"));
				uds[i] = ud;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return uds;
	}
	
	public boolean checkOneRecord(String id) {
		boolean flag = false;
		ResultSet re;
		try {
			String sql="select * from userData where id='"
					+ id
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				flag = true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public static void main(String[] args) {
		UserDataDao udd = new UserDataDao();
//		UserData ud = new UserData();
//		ud.setId("33564");
//		System.out.println(udd.queryOneRecord(ud));
//		System.out.println(ud.getEmail());
		System.out.println(udd.getCount());
	}
	
}
